package todo.core.testdb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.apache.log4j.Logger;

public class DerbyManager {
	// database properties
	private static String framework = "embedded";
	private static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private static String url = "jdbc:derby:derbyDB";
	private static Logger log = Logger.getLogger(DerbyManager.class);
	
	   /**
     * Loads the appropriate JDBC driver for this environment/framework. For
     * example, if we are in an embedded environment, we load Derby's
     * embedded Driver, <code>org.apache.derby.jdbc.EmbeddedDriver</code>.
     */
    private static void loadDriver() {
        /*
         *  The JDBC driver is loaded by loading its class.
         *  If you are using JDBC 4.0 (Java SE 6) or newer, JDBC drivers may
         *  be automatically loaded, making this code optional.
         *
         *  In an embedded environment, this will also start up the Derby
         *  engine (though not any databases), since it is not already
         *  running. In a client environment, the Derby engine is being run
         *  by the network server framework.
         *
         *  In an embedded environment, any static Derby system properties
         *  must be set before loading the driver to take effect.
         */
        try {
            Class.forName(driver).newInstance();
           log.info("Loaded the appropriate driver");
        } catch (ClassNotFoundException cnfe) {
            log.error("\nUnable to load the JDBC driver " + driver);
            log.error("Please check your CLASSPATH.", cnfe);
        } catch (InstantiationException ie) {
        	log.error(
                        "\nUnable to instantiate the JDBC driver " + driver, ie);
        } catch (IllegalAccessException iae) {
        	log.error(
                        "\nNot allowed to access the JDBC driver " + driver, iae);
        }
    }


	public static void createDatabase() throws Exception {
		// create database
		loadDriver();
		Connection conn = null;
        Properties props = new Properties(); // connection properties
        props.put("user", "user1");
        props.put("password", "user1");
        
	    conn = DriverManager.getConnection(url
                + ";create=true", props);
	    
	    log.info("Connected to and created database at " + url);
	    Statement s = conn.createStatement();
	    s.execute("create table todo(todo_id varchar(32), title varchar(400), description varchar(1000), due_dt date, complete char(1) )");
	    log.info("Todo table created");
		
	}


	public static void cleanupDatabase() throws Exception {
		// remove database
		Connection conn = null;
        Properties props = new Properties(); // connection properties
        props.put("user", "user1");
        props.put("password", "user1");
        
	    conn = DriverManager.getConnection(url
                + ";create=true", props);
	    Statement s = conn.createStatement();
		s.execute("drop table todo");
		log.info("Todo table droped");
		
        try
        {
            // the shutdown=true attribute shuts down Derby
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        }
        catch (SQLException se)
        {
            if (( (se.getErrorCode() == 50000)
                    && ("XJ015".equals(se.getSQLState()) ))) {
                // we got the expected exception
                log.info("Derby shut down normally");
                // Note that for single database shutdown, the expected
                // SQL state is "08006", and the error code is 45000.
            } else {
                // if the error code or SQLState is different, we have
                // an unexpected exception (shutdown failed)
                log.error("Derby did not shut down normally");
            }
        }


		
	}

}
